﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.SS.Formula.Functions;
using NPOI.XSSF.Model;
using IntelligentEscalator.BUS;
using IntelligentEscalator.Models;

namespace IntelligentEscalator.DAL
{
    public class RepairWorkOrderDAL
    {
        public int QuerTodayRepairCount()
        {
            string strSql = "select COUNT(1) from repair_workorder where to_days(preparationTime) = to_days(now()) AND workorderNO IS NOT NULL";
            int count = SqlHelper<RepairWorkOrderInfo>.SqlScalar(strSql);

            return count;
        }

        public string AddWorkOrder(RepairWorkOrderInfo workorderinfo)
        {
            string currenttime = DateTime.Now.ToString();
            string strSql = string.Format("INSERT INTO repair_workorder (elevatorId,workorderNO,workorderStateID,workordeType,creator,reportPerson,reportPhone,reportTime,faultCause,receiver,receiverNo,receiverPhone,repair_information,faultContentid,preparationTime,receiveTime,arriveTime,completeTime,acceptanceTime,isDelete,workattribute,reportSource,maintenanceTime) VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}',{7},'{8}','{9}','{10}','{11}','{12}',{13},{14},{15},{16},{17},{18},0,3,'{19}',{20})",
                workorderinfo.elevatorId, workorderinfo.workorderNO, workorderinfo.workorderStateID, workorderinfo.workordeType, workorderinfo.creator, workorderinfo.reportPerson, workorderinfo.reportPhone, workorderinfo.reportTime, workorderinfo.faultCause, workorderinfo.receiver, workorderinfo.receiverNo, workorderinfo.receiverPhone, workorderinfo.repair_information, workorderinfo.faultContentid, workorderinfo.preparationTime, workorderinfo.receiveTime, workorderinfo.arriveTime, workorderinfo.completeTime, workorderinfo.acceptanceTime, workorderinfo.reportSource, workorderinfo.maintenanceTime);

            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditWorkOrder(RepairWorkOrderInfo workorderinfo)
        {
            string strSql = string.Format("UPDATE repair_workorder set workorderStateID={0},workordeType={1},reportPerson='{2}',reportPhone='{3}',reportTime={4},faultCause='{5}',receiver='{6}',receiverNo='{7}',receiverPhone='{8}',repair_information='{9}',faultContentid={10},receiveTime={12},arriveTime={13},completeTime={14},acceptanceTime={15},maintenanceTime={17} WHERE id={16}",
                workorderinfo.workorderStateID, workorderinfo.workordeType, workorderinfo.reportPerson, workorderinfo.reportPhone, workorderinfo.reportTime, workorderinfo.faultCause, workorderinfo.receiver, workorderinfo.receiverNo, workorderinfo.receiverPhone, workorderinfo.repair_information, workorderinfo.faultContentid, workorderinfo.preparationTime, workorderinfo.receiveTime, workorderinfo.arriveTime, workorderinfo.completeTime, workorderinfo.acceptanceTime, workorderinfo.id, workorderinfo.maintenanceTime);
            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<UserInfo> QueryAllRepairman()
        {
            string strSql = @"SELECT
	                            a.id,
	                            a.fullname,
	                            a.username,
	                            b.groupID,
	                            c.groupNO,
	                            a.roleid
                            FROM
	                            (
		                            USER a
		                            LEFT JOIN organstruct b ON a.id = b.userID
	                            )
                            LEFT JOIN organ c ON b.groupID = c.id
                            WHERE
	                            a.isDelete = 0
                            AND a.roleid = 3";
            return SqlHelper<UserInfo>.SqlList(strSql);
        }

        public string DelWorkOrder(int orderid)
        {
            string strSql = string.Format("delete FROM repair_workorder WHERE id={0}", orderid);

            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public RepairWorkOrderInfo LoadWorkOrder(int orderid)
        {
            string strSql = string.Format("SELECT a.*,b.fullname AS receiveName FROM vi_repairorderinfo a LEFT JOIN `user` b ON a.receiverID=b.id WHERE a.id={0}", orderid);
            return SqlHelper<RepairWorkOrderInfo>.SqlSingleData(strSql);
        }

        public List<RepairWorkOrderInfo> QueryWorkOrder(RepairWorkOrderInfo workorderinfo)
        {
            try
            {
                List<RepairWorkOrderInfo> list = new List<RepairWorkOrderInfo>();
                string strSql = "SELECT * FROM vi_repairorderinfo WHERE workorderStateID!=4";

                if (!string.IsNullOrEmpty(workorderinfo.elevatorName))
                {
                    strSql += " AND elevatorName like @elevatorName";
                }
                if (!string.IsNullOrEmpty(workorderinfo.startTime))
                {
                    strSql += " AND DATE(preparationTime)>=DATE(@.startTime)";
                }
                if (!string.IsNullOrEmpty(workorderinfo.endTime))
                {
                    strSql += " AND DATE(preparationTime)<=DATE(@endTime)";
                }

                strSql += " ORDER BY preparationTime DESC";
                if (workorderinfo.Recordhead > -1 && workorderinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + workorderinfo.elevatorName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(workorderinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(workorderinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", workorderinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", workorderinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<RepairWorkOrderInfo>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }

        }

        public int QueryWorkOrderCount(RepairWorkOrderInfo workorderinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_repairorderinfo WHERE workorderStateID!=4";
                if (!string.IsNullOrEmpty(workorderinfo.elevatorName))
                {
                    strSql += " AND elevatorName like @elevatorName";
                }
                if (!string.IsNullOrEmpty(workorderinfo.startTime))
                {
                    strSql += " AND DATE(preparationTime)>=DATE(@.startTime)";
                }
                if (!string.IsNullOrEmpty(workorderinfo.endTime))
                {
                    strSql += " AND DATE(preparationTime)<=DATE(@endTime)";
                }
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + workorderinfo.elevatorName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(workorderinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(workorderinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {
                return count;
            }
            return count;
        }

        public string UpdateReadState(int id)
        {
            string strSql = string.Format("UPDATE fault_report SET isRead=1 WHERE id={0}", id);

            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<RepairWorkOrderInfo> QueryFaultCount(RepairWorkOrderInfo workorderinfo)
        {
            try
            {
                List<RepairWorkOrderInfo> list = new List<RepairWorkOrderInfo>();
                string strSql = "SELECT * FROM repair_workorder WHERE workorderStateID IS NULL AND workorderNO IS NULL AND isDelete=0 AND isRead=@isRead";
                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@isRead", workorderinfo.isRead);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<RepairWorkOrderInfo>(dataTable);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public string AddDeviceFalutWorkOrder(RepairWorkOrderInfo workorderinfo)
        {
            string strSql = string.Format("INSERT INTO repair_workorder (ElevatorID,Creator,ReportPerson,ReportPhone,ReportTime,Fault_Type,Fault_Cause,IsDelete,Workattribute,Remark,PreparationTime,IsTiring) VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}',0,3,'{7}','{8}',{9});select @@IDENTITY AS ID;",
                workorderinfo.elevatorId, workorderinfo.creator, workorderinfo.reportPerson, workorderinfo.reportPhone, workorderinfo.reportTime, workorderinfo.faultType, workorderinfo.faultCause, workorderinfo.remark, DateTime.Now.ToString(), workorderinfo.isTiring);
            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlScalar(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string CheckWorkNo(string workno)
        {
            string strsql = "select workorderNO FROM repair_workorder WHERE workorderNO='" + workno + "'";
            return SqlHelper<RepairWorkOrderInfo>.SqlScalarString(strsql);
        }

        public List<RepairWorkOrderInfo> QueryWorkOrderHistory(RepairWorkOrderInfo workorderinfo)
        {
            try
            {
                List<RepairWorkOrderInfo> list = new List<RepairWorkOrderInfo>();
                string strSql = "SELECT vi_repairorderinfo.*,area.areaName AS mainAreaName FROM vi_repairorderinfo LEFT JOIN area ON vi_repairorderinfo.mainAreaId=area.id WHERE vi_repairorderinfo.workorderStateID=4";
                if (workorderinfo.elevatorId > 0)
                {
                    strSql += " AND vi_repairorderinfo.elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(workorderinfo.elevatorName))
                {
                    strSql += " AND vi_repairorderinfo.elevatorName like @elevatorName";
                }

                if (!string.IsNullOrEmpty(workorderinfo.startTime))
                {
                    strSql += " AND DATE(vi_repairorderinfo.preparationTime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(workorderinfo.endTime))
                {
                    strSql += " AND DATE(vi_repairorderinfo.preparationTime)<=DATE(@endTime)";
                }

                strSql += " ORDER BY vi_repairorderinfo.preparationTime DESC";

                if (workorderinfo.Recordhead > -1 && workorderinfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + workorderinfo.elevatorName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", workorderinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(workorderinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(workorderinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", workorderinfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", workorderinfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<RepairWorkOrderInfo>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        public List<RepairWorkOrderInfo> QueryWorkOrderHistoryByIds(RepairWorkOrderInfo workorderinfo)
        {
            string strSql = "SELECT vi_repairorderinfo.*,area.areaName AS mainAreaName FROM vi_repairorderinfo LEFT JOIN area ON vi_repairorderinfo.mainAreaId=area.id WHERE vi_repairorderinfo.id in(" + workorderinfo.ids + ")";

            return SqlHelper<RepairWorkOrderInfo>.SqlList(strSql);
        }

        public int QueryWorkOrderHistoryCount(RepairWorkOrderInfo workorderinfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_repairorderinfo WHERE workorderStateID=4";

                if (workorderinfo.elevatorId > 0)
                {
                    strSql += " AND vi_repairorderinfo.elevatorId=@elevatorId";
                }
                if (!string.IsNullOrEmpty(workorderinfo.elevatorName))
                {
                    strSql += " AND vi_repairorderinfo.elevatorName like @elevatorName";
                }

                if (!string.IsNullOrEmpty(workorderinfo.startTime))
                {
                    strSql += " AND DATE(vi_repairorderinfo.preparationTime)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(workorderinfo.endTime))
                {
                    strSql += " AND DATE(vi_repairorderinfo.preparationTime)<=DATE(@endTime)";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + workorderinfo.elevatorName + "%";
                    mySqlCommand.Parameters.AddWithValue("@elevatorName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", workorderinfo.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(workorderinfo.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(workorderinfo.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public string AddFaultReport(FaultReportInfo reportinfo)
        {
            string strSql = string.Format("INSERT INTO fault_report (elevatorID,faultTime,faultCode,faultSource) VALUES({0},'{1}','{2}','{3}')", reportinfo.elevatorID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), reportinfo.faultCode, reportinfo.faultSource);

            try
            {
                int count = SqlHelper<FaultReportInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateAnnuallyRead(int id)
        {
            string strSql = string.Format("UPDATE routine_maintain SET isRead=1 WHERE id={0}", id);

            try
            {
                int count = SqlHelper<MaintainInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }
        public string UpdateElevatorRead(int id)
        {
            string strSql = string.Format("UPDATE elevator SET isRead=1 WHERE id={0}", id);

            try
            {
                int count = SqlHelper<Elevator>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateAllElevatorRead()
        {
            string strSql = "UPDATE elevator SET isRead=1 WHERE bend>bendThreshold*0.9 AND bendThreshold>0";

            try
            {
                int count = SqlHelper<Elevator>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public int QueryFaultReportCount()
        {
            string strSql = string.Format("SELECT COUNT(1) FROM fault_report WHERE DATE(faultTime)=DATE('{0}')", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            int count = 0;
            try
            {
                count = SqlHelper<RepairWorkOrderInfo>.SqlScalar(strSql);
            }
            catch
            {
                return count;
            }
            return count;
        }

        public int QueryAllFaultReportCount()
        {
            string strSql = string.Format("SELECT COUNT(1) FROM fault_report");
            int count = 0;
            try
            {
                count = SqlHelper<RepairWorkOrderInfo>.SqlScalar(strSql);
            }
            catch
            {
                return count;
            }
            return count;
        }

        public List<FaultReportInfo> QueryFaultHistory(FaultReportInfo faultReport)
        {
            try
            {
                List<FaultReportInfo> list = new List<FaultReportInfo>();
                string strSql = "SELECT vi_fault.*,area.areaName AS mainAreaName FROM vi_fault LEFT JOIN area ON vi_fault.mainAreaId=area.id";

                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(faultReport.elevatorIds))
                {
                    sqlwhere += " vi_fault.elevatorID IN(" + faultReport.elevatorIds + ") AND";
                }
                if (faultReport.elevatorID > 0)
                {
                    sqlwhere += " vi_fault.elevatorID=@elevatorID AND";
                }

                if (!string.IsNullOrEmpty(faultReport.startTime))
                {
                    sqlwhere += " DATE(vi_fault.faultTime)>=DATE(@startTime) AND";
                }
                if (!string.IsNullOrEmpty(faultReport.endTime))
                {
                    sqlwhere += " AND DATE(vi_fault.faultTime)<=DATE(@endTime) AND";
                }
                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                if (faultReport.Recordhead > -1 && faultReport.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", faultReport.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(faultReport.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(faultReport.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", faultReport.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", faultReport.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<FaultReportInfo>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        public List<FaultReportInfo> QueryFaultHistoryByIds(FaultReportInfo faultReport)
        {
            string strSql = "SELECT vi_fault.*,area.areaName AS mainAreaName FROM vi_fault LEFT JOIN area ON vi_fault.mainAreaId=area.id WHERE vi_fault.id in(" + faultReport.ids + ")";

            return SqlHelper<FaultReportInfo>.SqlList(strSql);
        }

        public int QueryFaultHistoryCount(FaultReportInfo faultReport)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_fault";

                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(faultReport.elevatorIds))
                {
                    sqlwhere += " elevatorID IN(" + faultReport.elevatorIds + ") AND";
                }
                if (faultReport.elevatorID > 0)
                {
                    sqlwhere += " vi_fault.elevatorID=@elevatorID AND";
                }

                if (!string.IsNullOrEmpty(faultReport.startTime))
                {
                    sqlwhere += " DATE(vi_fault.faultTime)>=DATE(@startTime) AND";
                }
                if (!string.IsNullOrEmpty(faultReport.endTime))
                {
                    sqlwhere += " AND DATE(vi_fault.faultTime)<=DATE(@endTime) AND";
                }
                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorID", faultReport.elevatorID);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(faultReport.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(faultReport.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public string BatchDelWorkOrder(string ids)
        {
            string strSql = string.Format("delete FROM repair_workorder WHERE id in({0})", ids);

            try
            {
                int count = SqlHelper<RepairWorkOrderInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<FaultReportInfo> QueryFaultByElevatorIds(Elevator elevator)
        {
            string strSql = "SELECT * FROM vi_fault";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(elevator.starttime))
            {
                sqlwhere += " DATE(faultTime)>=DATE('" + elevator.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevator.endtime))
            {
                sqlwhere += " DATE(faultTime)<=DATE('" + elevator.endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevator.elevatorIds))
            {
                sqlwhere += " elevatorID in(" + elevator.elevatorIds + ") AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (elevator.Recordhead > -1 && elevator.Recordtail > 0)
            {
                strSql += " LIMIT " + elevator.Recordhead + "," + elevator.Recordtail;
            }

            return SqlHelper<FaultReportInfo>.SqlList(strSql);
        }

        public int QueryFaultByElevatorIdsCount(Elevator elevator)
        {
            string strSql = "SELECT COUNT(1) FROM vi_fault";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(elevator.starttime))
            {
                sqlwhere += " DATE(faultTime)>=DATE('" + elevator.starttime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevator.endtime))
            {
                sqlwhere += " DATE(faultTime)<=DATE('" + elevator.endtime + "') AND";
            }
            if (!string.IsNullOrEmpty(elevator.elevatorIds))
            {
                sqlwhere += " elevatorID in(" + elevator.elevatorIds + ") AND";
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where " + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<FaultReportInfo>.SqlScalar(strSql);
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
